﻿using BonjourQuotation.DataAccess.Entity_Framework;
using BonjourQuotation.ObjectModel.AccountData;
using BonjourQuotation.ObjectModel.BusinessData;
using BonjourQuotation.ObjectModel.ErrorData;
using BonjourQuotation.ObjectModel.LoggerData;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using log4net;

namespace BonjourQuotation.DataAccess.Repositories
{
    public class AccountRepository : IAccountRepository
    {
        private readonly BonDataEntities _context = new BonDataEntities();
        private readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public LoginResponseData Login(LoginRequestData UserData)
        {
            LoginResponseData data = new LoginResponseData();
            data.IsAuth = false;
            try
            {
                var User = _context.Users.Where(user => user.UserName == UserData.UserName && user.Password == UserData.Password).SingleOrDefault();
                if (User != null)
                {
                    data.IsAuth = true;
                    data.Role = User.Role;
                }
            }
            catch (Exception ex)
            {
                data.Role = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return data;
        }
        public CreateUserResponseData CreateUser(CreateUserRequestData UserData)
        {
            CreateUserResponseData ResponseData = new CreateUserResponseData();
            ResponseData.Error = new ErrorBInfo();
            // check username
            var User = _context.Users.Where(user => user.UserName == UserData.UserName).SingleOrDefault();
            if (User != null)
            {
                ResponseData.Error.errorCode = 101;
                ResponseData.Error.Message = "Username allready exists.";
            }
            else
            {
                tblEmployee NewEmployee = new tblEmployee();
                User NewUser = new User() { Password = UserData.Password, UserName = UserData.UserName, Role = UserData.Role };
                using (var transaction = _context.Database.BeginTransaction())
                {
                    try
                    {
                        _context.tblEmployees.Add(NewEmployee);
                        _context.SaveChanges();
                        NewUser.EmployeeID = NewEmployee.EmployeeID;
                        _context.Users.Add(NewUser);
                        _context.SaveChanges();
                        transaction.Commit();
                        ResponseData.EmployeeID = NewEmployee.EmployeeID;
                    }
                    catch (Exception ex)
                    {
                        ResponseData.Error.errorCode = 100;
                        ResponseData.Error.Message = ex.Message;
                        logger.Error(ex.ToMessageAndCompleteStacktrace());
                        transaction.Rollback();
                    }
                }
            }
            return ResponseData;
        }
        public List<ManageUsersGridBData> GetAllUsers()
        {
            return (from user in _context.Users select new ManageUsersGridBData() { Role = user.Role, UserID = user.UserID, UserName = user.UserName, EmployeeID = user.EmployeeID }).ToList();
        }
        public UserDetailsBData GetUserDetailsByEmpID(int EmployeeID)
        {
            var UserData = (from Emp in _context.tblEmployees
                            where Emp.EmployeeID == EmployeeID
                            select new UserDetailsBData
                            {
                                EmployeeID = Emp.EmployeeID,
                                FirstName = Emp.FirstName,
                                LastName = Emp.LastName,
                                MiddleName = Emp.MiddleName,
                                Title = Emp.Title,
                                SelectedDepartmentName = Emp.DepartmentName,
                                DateHired = Emp.DateHired,
                                Extension = Emp.Extension,
                                Active = Emp.Active,
                                Note = Emp.Note,
                                SocialSecurityNumber = Emp.SocialSecurityNumber,
                                BirthDate = Emp.BirthDate,
                                SelectedStateID = Emp.StateID,
                                SelectedSex = Emp.Sex,
                                Address = Emp.Address,
                                SelectedCity = Emp.City,
                                CityID = Emp.CityID,
                                EmailName = Emp.EmailName,
                                SelectedCountry = Emp.Country,
                                SelectedLCountryID = Emp.LCountryID,
                                PostalCode = Emp.PostalCode,
                                EmrgcyContactName = Emp.EmrgcyContactName,
                                EmrgcyContactPhone = Emp.EmrgcyContactPhone,
                                HomePhone = Emp.HomePhone
                            }).SingleOrDefault();
            if (UserData.SelectedLCountryID == 1)
            {
                UserData.StateID = (from state in _context.tblStates where state.Active == true orderby state.StateID select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
                if (UserData.SelectedStateID == null)
                {
                    UserData.City = new Dictionary<int, string>();
                }
                else
                {
                    UserData.City = (from city in _context.tblCities where city.StateID == UserData.SelectedStateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                    if (UserData.City == null)
                        UserData.City = new Dictionary<int, string>();
                    if (UserData.CityID == null && UserData.SelectedCity != null && UserData.SelectedCity != "")
                    {
                        UserData.City.Add(-1, UserData.SelectedCity);
                        UserData.CityID = -1;
                    }
                }
            }
            else
            {
                UserData.StateID = new Dictionary<string, string>();
                if (UserData.SelectedLCountryID == null)
                    UserData.City = new Dictionary<int, string>();
                else
                {
                    UserData.City = (from city in _context.tblCities where city.CountryID == UserData.SelectedLCountryID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                    if (UserData.City == null)
                        UserData.City = new Dictionary<int, string>();
                    if (UserData.CityID == null && UserData.SelectedCity != null && UserData.SelectedCity != "")
                    {
                        UserData.City.Add(-1, UserData.SelectedCity);
                        UserData.CityID = -1;
                    }
                }
            }
            UserData.DepartmentName = (from dep in _context.tblDepartments select new { Key = dep.DepartmentID, Value = dep.DepartmentName }).ToDictionary(o => o.Key, o => o.Value);
            UserData.Sex = new Dictionary<int, string> { 
               {0, "Mr." },
               {1, "Mrs." },
               {2, "Ms." },
               {3, "Dr." },
           };
            UserData.Country = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.Nationality select new { Key = cnt.CountryID, Value = cnt.Nationality }).ToDictionary(o => o.Key, o => o.Value);
            UserData.LCountry = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            UserData.QuotaRegion = (from QRegion in _context.tblQuotaRegions where QRegion.Active == true select new { Key = QRegion.RegionID, Value = QRegion.Region }).ToDictionary(o => o.Key, o => o.Value);
            UserData.SelectedQuotaRegions = (from UserQRegion in _context.UsersQuotaRegions where UserQRegion.EmployeeID == EmployeeID select UserQRegion.QuotaRegionID).ToList();
            UserData.SupervisorID = (from user in _context.Users from emp in _context.tblEmployees where user.EmployeeID == emp.EmployeeID select new { Key = emp.EmployeeID, Value = emp.FirstName + ", " + emp.LastName }).ToDictionary(o => o.Key, o => o.Value);
            UserData.SelectedSupervisorID = (from SupEmp in _context.tblSupEmployees where SupEmp.EmployeeID == EmployeeID select SupEmp.SupervisorID).ToList();
            return UserData;
        }
        public string GetUserName(int UserID)
        {
            var UserName = (from user in _context.Users where user.UserID == UserID select user.UserName).SingleOrDefault();
            return UserName;
        }
        public ErrorBInfo PostUserDetailsData(UserDetailsBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    var EditUserQuotaRegion = _context.UsersQuotaRegions.Where(reg => reg.EmployeeID == Data.EmployeeID).ToList();
                    var EditUserQuotaRegionID = EditUserQuotaRegion.Select(reg => reg.QuotaRegionID).ToList();
                    var NewRegID = Data.SelectedQuotaRegions.Where(regID => !EditUserQuotaRegionID.Contains(regID)).ToList();
                    var DelRegID = EditUserQuotaRegionID.Where(regID => !Data.SelectedQuotaRegions.Contains(regID)).ToList();
                    var DelQuotaRegion = EditUserQuotaRegion.Where(reg => DelRegID.Contains(reg.QuotaRegionID)).ToList();
                    foreach (var ID in NewRegID)
                    {
                        UsersQuotaRegion Reg = new UsersQuotaRegion()
                        {
                            EmployeeID = Data.EmployeeID,
                            QuotaRegionID = ID,
                        };
                        _context.UsersQuotaRegions.Add(Reg);
                    }
                    _context.UsersQuotaRegions.RemoveRange(DelQuotaRegion);
                    
                    var EditUserSupervisor = _context.tblSupEmployees.Where(sup => sup.EmployeeID == Data.EmployeeID).ToList();
                    var EditUserSupervisorID = EditUserSupervisor.Select(sup => sup.SupervisorID).ToList();
                    var NewSupID = Data.SelectedSupervisorID.Where(supID => !EditUserSupervisorID.Contains(supID)).ToList();
                    var DelSupID = EditUserSupervisorID.Where(supID => !Data.SelectedSupervisorID.Contains(supID)).ToList();
                    var DelSupervisor = EditUserSupervisor.Where(sup => DelSupID.Contains(sup.SupervisorID)).ToList(); 
                    foreach(var ID in NewSupID)
                    {
                        tblSupEmployee SupEmp = new tblSupEmployee()
                        {
                            EmployeeID = Data.EmployeeID,
                            SupervisorID = ID,
                        };
                        _context.tblSupEmployees.Add(SupEmp);
                    }
                    _context.tblSupEmployees.RemoveRange(DelSupervisor);

                    var EditEmployee = (from Employee in _context.tblEmployees where Employee.EmployeeID == Data.EmployeeID select Employee).SingleOrDefault();
                    if (EditEmployee != null)
                    {
                        EditEmployee.FirstName = Data.FirstName;
                        EditEmployee.LastName = Data.LastName;
                        EditEmployee.MiddleName = Data.MiddleName;
                        EditEmployee.Title = Data.Title;
                        EditEmployee.DepartmentName = Data.SelectedDepartmentName;
                       
                        EditEmployee.DateHired = Data.DateHired;
                        EditEmployee.Extension = Data.Extension;
                        EditEmployee.SocialSecurityNumber = Data.SocialSecurityNumber;
                        EditEmployee.Sex = Data.SelectedSex;
                        EditEmployee.Address = Data.Address;
                        EditEmployee.HomePhone = Data.HomePhone;
                        EditEmployee.City = Data.CityID == -1 ? Data.SelectedCity : (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault();
                        EditEmployee.CityID = Data.CityID != -1 ? Data.CityID : null;
                        EditEmployee.StateID = Data.SelectedStateID;
                        EditEmployee.PostalCode = Data.PostalCode;
                        EditEmployee.BirthDate = Data.BirthDate;
                        EditEmployee.Country = Data.SelectedCountry;
                        EditEmployee.LCountryID = Data.SelectedLCountryID;
                        EditEmployee.EmailName = Data.EmailName;
                        EditEmployee.EmrgcyContactName = Data.EmrgcyContactName;
                        EditEmployee.EmrgcyContactPhone = Data.EmrgcyContactPhone;
                        EditEmployee.Active = Data.Active;
                        EditEmployee.Note = Data.Note;
                        _context.SaveChanges();
                    }
                    else
                    {
                        Error.errorCode = 130;
                        Error.Message = "Employee Can`t be found:";
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        public ErrorBInfo SaveProfileData(string UserName, int ProfileID, string ProfileName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var CreateUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
            if (!(_context.tblProfiles.Any(prof => prof.ProfileName == ProfileName && prof.ProfileID != ProfileID && prof.UserID == CreateUserID)))
            {
                try
                {
                    if (ProfileID == 0)
                    {
                        tblProfile NewProf = new tblProfile()
                        {
                            ProfileName = ProfileName,
                            UserID = CreateUserID,
                        };
                        _context.tblProfiles.Add(NewProf);
                    }
                    else
                    {
                        var EditProfile = (from prof in _context.tblProfiles where prof.ProfileID == ProfileID && prof.UserID == CreateUserID select prof).SingleOrDefault();
                        EditProfile.ProfileName = ProfileName;
                    }
                    _context.SaveChanges();
                    Error.errorCode = 0;
                }
                catch(Exception ex)
                {
                    Error.errorCode = 100;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                }
            }
            else
            {
                Error.errorCode = 105;
                Error.Message = "Profile Name Must be Unique";
            }
            return Error;
        }
        public ErrorBInfo DeleteUserById(int UserId)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var user = _context.Users.Where(usr => usr.UserID == UserId).SingleOrDefault();
            using (var transaction = _context.Database.BeginTransaction())
            {
                if(user != null)
                {
                    var empl = _context.tblEmployees.Where(emp => emp.EmployeeID == user.EmployeeID).SingleOrDefault();
                    if(empl != null)
                    {
                        try
                        {
                            _context.tblEmployees.Remove(empl);
                            _context.Users.Remove(user);
                            _context.SaveChanges();
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            Error.errorCode = 103;
                            Error.Message = "This User use from another table";
                            logger.Error(ex.ToMessageAndCompleteStacktrace());
                            transaction.Rollback();
                        }
                    }
                }  
            }
            return Error;
        }
        public EditUserBData GetEditUserData(int UserId)
        {
            EditUserBData EditData = new EditUserBData();
            try
            {
                EditData = (from user in _context.Users where user.UserID == UserId select new EditUserBData { UserID = user.UserID, UserName = user.UserName, SelectedRole = user.Role }).SingleOrDefault();
            }
            catch (Exception ex)
            {
                EditData.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return EditData;
        }
        public ErrorBInfo PostEditUserData(EditUserBData UserData)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var EditUser = (from user in _context.Users where user.UserID == UserData.UserID select user).SingleOrDefault();
                if (EditUser != null && EditUser.Password == UserData.OldPassword)
                {
                    var CanCreateUser = (from user in _context.Users where user.UserID != UserData.UserID && user.UserName == UserData.UserName select user).SingleOrDefault();
                    if (CanCreateUser == null)
                    {
                        EditUser.UserName = UserData.UserName;
                        EditUser.Password = UserData.Password;
                        EditUser.Role = UserData.SelectedRole;
                        _context.SaveChanges();
                    }
                    else
                    {
                        Error.errorCode = 101;
                        Error.Message = "Username allready exists.";
                    }
                }
                else
                {
                    Error.errorCode = 121;
                    Error.Message = "Old password is wrong.";
                }
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public ToolBData LoadTools()
        {
            ToolBData Data = new ToolBData();
            Data.Tool = (from tool in _context.tblTools select new { Key = tool.ToolID, Value = tool.ToolName }).ToDictionary(o => o.Key, o => o.Value);
            return Data;
        }
        public ProfileBData LoadProfiles(string UserName)
        {
            ProfileBData Data = new ProfileBData();
            var UserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
            Data.Profile = (from profile in _context.tblProfiles where profile.UserID == UserID orderby profile.ProfileID descending select new { Key = profile.ProfileID, Value = profile.ProfileName }).ToDictionary(o => o.Key, o => o.Value);
            return Data;
        }
        public ToolBData LoadToolTab(int ToolID)
        {
            ToolBData Data = new ToolBData();
            Data.ToolTab = (from tooltab in _context.tblToolTabs where tooltab.ToolID == ToolID select new { Key = tooltab.ToolTabID, Value = tooltab.ToolTabName }).ToDictionary(o => o.Key, o => o.Value);
            return Data;
        }
        public ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData LoadToolTabColumnPermissionsCanViewCanEditHasFilter(int UserID, int ToolTabID)
        {
            ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData();
            ToolTabColumnPermissions.ToolTabColumnName = (from columnname in _context.tblToolTabColumns
                                                          where columnname.ToolTabID == ToolTabID
                                                          select new
                                                          {
                                                              Key = columnname.ToolTabColumnID,
                                                              Value = columnname.ToolTabColumnName,
                                                          }).ToDictionary(o => o.Key, o => o.Value);
            ToolTabColumnPermissions.UserID = UserID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabPermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData>();
            int LineNo = Permissions.Where(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count() + 1;
            if (Permissions.Any(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabPermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.UserID == UserID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     orderby permissions.LineNo
                                                     select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         CanEdit = permissions.CanEdit,
                                                         HasFilter = permissions.HasFilter,
                                                         LineNo = permissions.LineNo,
                                                         SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                     }).ToList();
                }
                else
                {
                    LineNo = permissionscount + 1;
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.UserID == UserID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabPermissions
                                              where permissions.UserID == UserID
                                              && permissions.ToolTabColumnID == Columns
                                              select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                              {
                                                  CanView = permissions.CanView,
                                                  CanEdit = permissions.CanEdit,
                                                  HasFilter = permissions.HasFilter,
                                                  LineNo = permissions.LineNo,
                                                  SelectedToolTabColumn = permissions.ToolTabColumnID,
                                              }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                   {
                                                       CanView = false,
                                                       CanEdit = false,
                                                       HasFilter = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       LineNo = LineNo,
                                                   }).SingleOrDefault();
                            ++LineNo;
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }
            }
            else
            {
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                 {
                                                     CanView = false,
                                                     CanEdit = false,
                                                     HasFilter = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                 }).ToList();
                int LineNoUnPermission = 1;
                foreach (var per in ToolTabColumnPermissions.Rows)
                {
                    per.LineNo = LineNoUnPermission;
                    ++LineNoUnPermission;
                }
            }
            var FixedRowCounts = _context.tblToolFixedColumnCounts.AsQueryable();
            if (FixedRowCounts.Any(rcount => rcount.UserID == UserID && rcount.ToolTabID == ToolTabID))
            {
                ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData Perm = new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData();
                Perm = (from fix in _context.tblToolFixedColumnCounts
                        where fix.UserID == UserID
                        && fix.ToolTabID == ToolTabID
                        select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData
                        {
                            FixedColumnCount = fix.FixedColumnCount,
                            ToolTabFixedColumnCountID = fix.ToolTabFixedColumnCountID,
                        }).FirstOrDefault();
                ToolTabColumnPermissions.FixedColumnCount = Perm.FixedColumnCount;
                ToolTabColumnPermissions.ToolTabFixedColumnCountID = Perm.ToolTabFixedColumnCountID;
            }
            else
            {
                ToolTabColumnPermissions.FixedColumnCount = 0;
                ToolTabColumnPermissions.ToolTabFixedColumnCountID = 0;
            }
            return ToolTabColumnPermissions;
        }
        public ToolTabColumnPermisionsCanViewGridBData LoadToolTabColumnPermissionsCanView(int UserID, int ToolTabID)
        {
            ToolTabColumnPermisionsCanViewGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewGridBData();
            ToolTabColumnPermissions.UserID = UserID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabPermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewGridRowBData>();
            if (Permissions.Any(per => per.UserID == UserID &&ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabPermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.UserID == UserID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     select new ToolTabColumnPermisionsCanViewGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         SelectedToolTabColumn = Column.ToolTabColumnID,
                                                         SelectedToolTabColumnName = Column.ToolTabColumnName,
                                                     }).ToList();
                }
                else
                {
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.UserID == UserID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabPermissions
                                              from Column in _context.tblToolTabColumns
                                              where permissions.UserID == UserID
                                              && permissions.ToolTabColumnID == Columns
                                              && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                              select new ToolTabColumnPermisionsCanViewGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                         SelectedToolTabColumnName = Column.ToolTabColumnName,
                                                     }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewGridRowBData
                                                   {
                                                       CanView = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       SelectedToolTabColumnName = column.ToolTabColumnName,
                                                   }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }
            }
            else
            {
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewGridRowBData
                                                 {
                                                     CanView = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                     SelectedToolTabColumnName = column.ToolTabColumnName,
                                                 }).ToList();
            }
            return ToolTabColumnPermissions;
        }
        public ToolTabColumnPermisionsCanViewCanEditGridBData LoadToolTabColumnPermissionsCanViewCanEdit(int UserID, int ToolTabID)
        {
            ToolTabColumnPermisionsCanViewCanEditGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewCanEditGridBData();
            ToolTabColumnPermissions.UserID = UserID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabPermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewCanEditGridRowBData>();
            if (Permissions.Any(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.UserID == UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabPermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.UserID == UserID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         CanEdit = permissions.CanEdit,
                                                         SelectedToolTabColumn = Column.ToolTabColumnID,
                                                         SelectedToolTabColumnName = Column.ToolTabColumnName,
                                                     }).ToList();
                }
                else
                {
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.UserID == UserID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabPermissions
                                              from Column in _context.tblToolTabColumns
                                              where permissions.UserID == UserID
                                              && permissions.ToolTabColumnID == Columns
                                              && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                              select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                              {
                                                  CanView = permissions.CanView,
                                                  CanEdit = permissions.CanEdit,
                                                  SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                  SelectedToolTabColumnName = Column.ToolTabColumnName,
                                              }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                   {
                                                       CanView = false,
                                                       CanEdit = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       SelectedToolTabColumnName = column.ToolTabColumnName,
                                                   }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }

            }
            else
            {
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                 {
                                                     CanView = false,
                                                     CanEdit = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                     SelectedToolTabColumnName = column.ToolTabColumnName,
                                                 }).ToList();
            }
            return ToolTabColumnPermissions;
        }
        public ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData LoadProfileToolTabColumnPermissionsCanViewCanEditHasFilter(string UserName, string ProfileName, int ProfileID, int ToolTabID)
        {
            var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
            if (ProfileID == 0)
            {
                ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
            }
            ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData();
            ToolTabColumnPermissions.ToolTabColumnName = (from columnname in _context.tblToolTabColumns
                                                          where columnname.ToolTabID == ToolTabID
                                                          select new
                                                          {
                                                              Key = columnname.ToolTabColumnID,
                                                              Value = columnname.ToolTabColumnName,
                                                          }).ToDictionary(o => o.Key, o => o.Value);
            ToolTabColumnPermissions.ProfileID = ProfileID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabProfilePermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData>();
            int LineNo = Permissions.Where(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count() + 1;
            if (Permissions.Any(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabProfilePermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.ProfileID == ProfileID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     orderby permissions.LineNo
                                                     select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         CanEdit = permissions.CanEdit,
                                                         HasFilter = permissions.HasFilter,
                                                         LineNo = permissions.LineNo,
                                                         SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                     }).ToList();
                }
                else
                {
                    LineNo = permissionscount + 1;
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.ProfileID == ProfileID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabProfilePermissions
                                              where permissions.ProfileID == ProfileID
                                              && permissions.ToolTabColumnID == Columns
                                              select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                              {
                                                  CanView = permissions.CanView,
                                                  CanEdit = permissions.CanEdit,
                                                  HasFilter = permissions.HasFilter,
                                                  LineNo = permissions.LineNo,
                                                  SelectedToolTabColumn = permissions.ToolTabColumnID,
                                              }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                   {
                                                       CanView = false,
                                                       CanEdit = false,
                                                       HasFilter = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       LineNo = LineNo,
                                                   }).SingleOrDefault();
                            ++LineNo;
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }
            }
            else
            {
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridRowBData
                                                 {
                                                     CanView = false,
                                                     CanEdit = false,
                                                     HasFilter = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                 }).ToList();
                int LineNoUnPermission = 1;
                foreach (var per in ToolTabColumnPermissions.Rows)
                {
                    per.LineNo = LineNoUnPermission;
                    ++LineNoUnPermission;
                }
            }
            var FixedRowCounts = _context.tblProfileToolFixedColumnCounts.AsQueryable();
            if (FixedRowCounts.Any(rcount => rcount.ProfileID == ProfileID && rcount.ToolTabID == ToolTabID))
            {
                ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData Perm = new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData();
                Perm = (from fix in _context.tblProfileToolFixedColumnCounts
                        where fix.ProfileID == ProfileID
                        && fix.ToolTabID == ToolTabID
                        select new ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData
                        {
                            FixedColumnCount = fix.FixedColumnCount,
                            ToolTabFixedColumnCountID = fix.ProfileToolTabFixedColumnCountID,
                        }).FirstOrDefault();
                ToolTabColumnPermissions.FixedColumnCount = Perm.FixedColumnCount;
                ToolTabColumnPermissions.ToolTabFixedColumnCountID = Perm.ToolTabFixedColumnCountID;
            }
            else
            {
                ToolTabColumnPermissions.FixedColumnCount = 0;
                ToolTabColumnPermissions.ToolTabFixedColumnCountID = 0;
            }
            return ToolTabColumnPermissions;
        }
        public ToolTabColumnPermisionsCanViewGridBData LoadProfileToolTabColumnPermissionsCanView(string UserName, string ProfileName, int ProfileID, int ToolTabID)
        {
            var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
            if (ProfileID == 0)
            {
                ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
            }
            ToolTabColumnPermisionsCanViewGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewGridBData();
            ToolTabColumnPermissions.ProfileID = ProfileID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabProfilePermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewGridRowBData>();
            if (Permissions.Any(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabProfilePermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.ProfileID == ProfileID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     select new ToolTabColumnPermisionsCanViewGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         SelectedToolTabColumn = Column.ToolTabColumnID,
                                                         SelectedToolTabColumnName = Column.ToolTabColumnName,
                                                     }).ToList();
                }
                else
                {
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.ProfileID == ProfileID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabProfilePermissions
                                              from Column in _context.tblToolTabColumns
                                              where permissions.ProfileID == ProfileID
                                              && permissions.ToolTabColumnID == Columns
                                              && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                              select new ToolTabColumnPermisionsCanViewGridRowBData
                                              {
                                                  CanView = permissions.CanView,
                                                  SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                  SelectedToolTabColumnName = Column.ToolTabColumnName,
                                              }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewGridRowBData
                                                   {
                                                       CanView = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       SelectedToolTabColumnName = column.ToolTabColumnName,
                                                   }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }

            }
            else
            {
                ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewGridRowBData>();
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewGridRowBData
                                                 {
                                                     CanView = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                     SelectedToolTabColumnName = column.ToolTabColumnName,
                                                 }).ToList();
            }
            return ToolTabColumnPermissions;
        }
        public ToolTabColumnPermisionsCanViewCanEditGridBData LoadProfileToolTabColumnPermissionsCanViewCanEdit(string UserName, string ProfileName, int ProfileID, int ToolTabID)
        {
            var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
            if (ProfileID == 0)
            {
                ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
            }
            ToolTabColumnPermisionsCanViewCanEditGridBData ToolTabColumnPermissions = new ToolTabColumnPermisionsCanViewCanEditGridBData();
            ToolTabColumnPermissions.ProfileID = ProfileID;
            ToolTabColumnPermissions.ToolTabID = ToolTabID;
            var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == ToolTabID select column.ToolTabColumnID).ToList();
            var Permissions = _context.tblToolTabProfilePermissions.AsQueryable();
            ToolTabColumnPermissions.Rows = new List<ToolTabColumnPermisionsCanViewCanEditGridRowBData>();
            if (Permissions.Any(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
            {
                var permissionscount = Permissions.Where(per => per.ProfileID == ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)).Count();
                if (permissionscount == ToolTabColumnID.Count)
                {
                    ToolTabColumnPermissions.Rows = (from permissions in _context.tblToolTabProfilePermissions
                                                     from Column in _context.tblToolTabColumns
                                                     where permissions.ProfileID == ProfileID
                                                     && Column.ToolTabID == ToolTabID
                                                     && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                                     select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                     {
                                                         CanView = permissions.CanView,
                                                         CanEdit = permissions.CanEdit,
                                                         SelectedToolTabColumn = Column.ToolTabColumnID,
                                                         SelectedToolTabColumnName = Column.ToolTabColumnName,
                                                     }).ToList();
                }
                else
                {
                    foreach (var Columns in ToolTabColumnID)
                    {
                        if (Permissions.Any(per => per.ProfileID == ProfileID && per.ToolTabColumnID == Columns))
                        {
                            var Permission = (from permissions in _context.tblToolTabProfilePermissions
                                              from Column in _context.tblToolTabColumns
                                              where permissions.ProfileID == ProfileID
                                              && permissions.ToolTabColumnID == Columns
                                              && permissions.ToolTabColumnID == Column.ToolTabColumnID
                                              select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                              {
                                                  CanView = permissions.CanView,
                                                  CanEdit = permissions.CanEdit,
                                                  SelectedToolTabColumn = permissions.ToolTabColumnID,
                                                  SelectedToolTabColumnName = Column.ToolTabColumnName,
                                              }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(Permission);

                        }
                        else
                        {
                            var NotinPermission = (from column in _context.tblToolTabColumns
                                                   where column.ToolTabColumnID == Columns
                                                   select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                   {
                                                       CanView = false,
                                                       CanEdit = false,
                                                       SelectedToolTabColumn = column.ToolTabColumnID,
                                                       SelectedToolTabColumnName = column.ToolTabColumnName,
                                                   }).SingleOrDefault();
                            ToolTabColumnPermissions.Rows.Add(NotinPermission);
                        }
                    }
                }

            }
            else
            {
                ToolTabColumnPermissions.Rows = (from column in _context.tblToolTabColumns
                                                 where column.ToolTabID == ToolTabID
                                                 select new ToolTabColumnPermisionsCanViewCanEditGridRowBData
                                                 {
                                                     CanView = false,
                                                     CanEdit = false,
                                                     SelectedToolTabColumn = column.ToolTabColumnID,
                                                     SelectedToolTabColumnName = column.ToolTabColumnName,
                                                 }).ToList();
            }
            return ToolTabColumnPermissions;
        }
        public ErrorBInfo SaveToolTabColumnPermissionsCanViewCanEditHasFilter(ToolTabColumnPermisionsCanViewCanEditHasFilterGridBData Data, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    if (Data.UserID != 0)
                    {
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabPermissions.Any(per => per.UserID == Data.UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeletePermissions = _context.tblToolTabPermissions.Where(per => per.UserID == Data.UserID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabPermissions.RemoveRange(DeletePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabPermission NewPerm = new tblToolTabPermission()
                            {
                                UserID = Data.UserID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanEdit = permission.CanEdit,
                                CanView = permission.CanView,
                                HasFilter = permission.HasFilter,
                                LineNo = permission.LineNo,
                            };
                            _context.tblToolTabPermissions.Add(NewPerm);
                        }
                        var EditFixedRowCount = (from Fix in _context.tblToolFixedColumnCounts where Fix.ToolTabFixedColumnCountID == Data.ToolTabFixedColumnCountID select Fix).FirstOrDefault();
                        if (EditFixedRowCount != null)
                        {
                            EditFixedRowCount.FixedColumnCount = Data.FixedColumnCount;
                        }
                        else
                        {
                            tblToolFixedColumnCount newfixedrow = new tblToolFixedColumnCount();
                            newfixedrow.FixedColumnCount = Data.FixedColumnCount;
                            newfixedrow.ToolTabID = Data.ToolTabID;
                            newfixedrow.UserID = Data.UserID;
                            _context.tblToolFixedColumnCounts.Add(newfixedrow);

                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                    else
                    {
                        var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
                        if (Data.ProfileID == 0)
                        {
                            if (_context.tblProfiles.Any(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID))
                            {
                                Data.ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
                            }
                        }
                        else
                        {
                            var EditProfile = _context.tblProfiles.Where(prof => prof.ProfileID == Data.ProfileID && prof.UserID == EditeUserID).SingleOrDefault();
                            if (EditProfile != null)
                            {
                                EditProfile.ProfileName = Data.ProfileName;
                                _context.SaveChanges();
                            }
                        }
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabProfilePermissions.Any(per => per.ProfileID == Data.ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeleteProfilePermissions = _context.tblToolTabProfilePermissions.Where(per => per.ProfileID == Data.ProfileID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabProfilePermissions.RemoveRange(DeleteProfilePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabProfilePermission NewPerm = new tblToolTabProfilePermission()
                            {
                                ProfileID = Data.ProfileID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanEdit = permission.CanEdit,
                                CanView = permission.CanView,
                                HasFilter = permission.HasFilter,
                                LineNo = permission.LineNo,
                            };
                            _context.tblToolTabProfilePermissions.Add(NewPerm);
                        }
                        var EditFixedRowCount = (from Fix in _context.tblProfileToolFixedColumnCounts where Fix.ProfileToolTabFixedColumnCountID == Data.ToolTabFixedColumnCountID select Fix).FirstOrDefault();
                        if (EditFixedRowCount != null)
                        {
                            EditFixedRowCount.FixedColumnCount = Data.FixedColumnCount;
                        }
                        else
                        {
                            tblProfileToolFixedColumnCount newfixedrow = new tblProfileToolFixedColumnCount();
                            newfixedrow.FixedColumnCount = Data.FixedColumnCount;
                            newfixedrow.ToolTabID = Data.ToolTabID;
                            newfixedrow.ProfileID = Data.ProfileID;
                            _context.tblProfileToolFixedColumnCounts.Add(newfixedrow);

                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    Error.errorCode = 200;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        public ErrorBInfo SaveToolTabColumnPermissionsCanView(ToolTabColumnPermisionsCanViewGridBData Data, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    if (Data.UserID != 0)
                    {
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabPermissions.Any(per => per.UserID == Data.UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeletePermissions = _context.tblToolTabPermissions.Where(per => per.UserID == Data.UserID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabPermissions.RemoveRange(DeletePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabPermission NewPerm = new tblToolTabPermission()
                            {
                                UserID = Data.UserID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanView = permission.CanView,
                            };
                            _context.tblToolTabPermissions.Add(NewPerm);
                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                    else
                    {
                        var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
                        if (Data.ProfileID == 0)
                        {
                            if (_context.tblProfiles.Any(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID))
                            {
                                Data.ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
                            }
                        }
                        else
                        {
                            var EditProfile = _context.tblProfiles.Where(prof => prof.ProfileID == Data.ProfileID && prof.UserID == EditeUserID).SingleOrDefault();
                            if (EditProfile != null)
                            {
                                EditProfile.ProfileName = Data.ProfileName;
                                _context.SaveChanges();
                            }
                        }
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabProfilePermissions.Any(per => per.ProfileID == Data.ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeleteProfilePermissions = _context.tblToolTabProfilePermissions.Where(per => per.ProfileID == Data.ProfileID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabProfilePermissions.RemoveRange(DeleteProfilePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabProfilePermission NewPerm = new tblToolTabProfilePermission()
                            {
                                ProfileID = Data.ProfileID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanView = permission.CanView,
                            };
                            _context.tblToolTabProfilePermissions.Add(NewPerm);
                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    Error.errorCode = 200;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        public ErrorBInfo SaveToolTabColumnPermissionsCanViewCanEdit(ToolTabColumnPermisionsCanViewCanEditGridBData Data, string UserName)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    if (Data.UserID != 0)
                    {
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabPermissions.Any(per => per.UserID == Data.UserID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeletePermissions = _context.tblToolTabPermissions.Where(per => per.UserID == Data.UserID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabPermissions.RemoveRange(DeletePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabPermission NewPerm = new tblToolTabPermission()
                            {
                                UserID = Data.UserID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanView = permission.CanView,
                                CanEdit = permission.CanEdit,
                            };
                            _context.tblToolTabPermissions.Add(NewPerm);
                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                    else
                    {
                        var EditeUserID = (from user in _context.Users where user.UserName == UserName select user.UserID).SingleOrDefault();
                        if (Data.ProfileID == 0)
                        {
                            if (_context.tblProfiles.Any(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID))
                            {
                                Data.ProfileID = _context.tblProfiles.Where(prof => prof.ProfileName == Data.ProfileName && prof.UserID == EditeUserID).Select(prof => prof.ProfileID).SingleOrDefault();
                            }
                        }
                        else
                        {
                            var EditProfile = _context.tblProfiles.Where(prof => prof.ProfileID == Data.ProfileID && prof.UserID == EditeUserID).SingleOrDefault();
                            if (EditProfile != null)
                            {
                                EditProfile.ProfileName = Data.ProfileName;
                                _context.SaveChanges();
                            }
                        }
                        var ToolTabColumnID = (from column in _context.tblToolTabColumns where column.ToolTabID == Data.ToolTabID select column.ToolTabColumnID).ToList();
                        if (_context.tblToolTabProfilePermissions.Any(per => per.ProfileID == Data.ProfileID && ToolTabColumnID.Contains(per.ToolTabColumnID)))
                        {
                            var ToolTabColumnIDs = Data.Rows.Select(column => column.SelectedToolTabColumn).ToList();
                            var DeleteProfilePermissions = _context.tblToolTabProfilePermissions.Where(per => per.ProfileID == Data.ProfileID && ToolTabColumnIDs.Contains(per.ToolTabColumnID)).ToList();
                            _context.tblToolTabProfilePermissions.RemoveRange(DeleteProfilePermissions);
                            _context.SaveChanges();
                        }
                        foreach (var permission in Data.Rows)
                        {
                            tblToolTabProfilePermission NewPerm = new tblToolTabProfilePermission()
                            {
                                ProfileID = Data.ProfileID,
                                ToolTabColumnID = permission.SelectedToolTabColumn,
                                CanView = permission.CanView,
                                CanEdit = permission.CanEdit,
                            };
                            _context.tblToolTabProfilePermissions.Add(NewPerm);
                        }
                        _context.SaveChanges();
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    Error.errorCode = 200;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        public ErrorBInfo ApplyProfileToUser(int UserID, int ProfileID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    var ProfilePermissions = _context.tblToolTabProfilePermissions.Where(per => per.ProfileID == ProfileID);
                    var ProfileToolFixedColumnCounts = (from Fix in _context.tblProfileToolFixedColumnCounts where Fix.ProfileID == ProfileID select Fix).ToList();
                    if(ProfilePermissions != null)
                    {
                        var DeleteUserPermission = _context.tblToolTabPermissions.Where(per => per.UserID == UserID);
                        _context.tblToolTabPermissions.RemoveRange(DeleteUserPermission);
                        _context.SaveChanges();
                        List<tblToolTabPermission> NewPermissions = new List<tblToolTabPermission>();
                        foreach(var profperm in ProfilePermissions)
                        {
                            tblToolTabPermission permission = new tblToolTabPermission();
                            permission.UserID = UserID;
                            permission.LineNo = profperm.LineNo;
                            permission.ToolTabColumnID = profperm.ToolTabColumnID;
                            permission.CanEdit = profperm.CanEdit;
                            permission.CanView = profperm.CanView;
                            permission.CanView = profperm.CanView;
                            permission.HasFilter = profperm.HasFilter;
                            NewPermissions.Add(permission);
                        }
                        _context.tblToolTabPermissions.AddRange(NewPermissions);
                    }
                    if (ProfileToolFixedColumnCounts != null)
                    {
                        var DeleteToolFixedColumnCounts = _context.tblToolFixedColumnCounts.Where(fix => fix.UserID == UserID);
                        _context.tblToolFixedColumnCounts.RemoveRange(DeleteToolFixedColumnCounts);
                        _context.SaveChanges();
                        List<tblToolFixedColumnCount> NewToolFixedColumnCount = new List<tblToolFixedColumnCount>();
                        foreach (var profToolFixedColumnCount in ProfileToolFixedColumnCounts)
                        {
                            tblToolFixedColumnCount FixedColumnCount = new tblToolFixedColumnCount();
                            FixedColumnCount.UserID = UserID;
                            FixedColumnCount.ToolTabID = profToolFixedColumnCount.ToolTabID;
                            FixedColumnCount.FixedColumnCount = profToolFixedColumnCount.FixedColumnCount;
                            NewToolFixedColumnCount.Add(FixedColumnCount);
                        }
                        _context.tblToolFixedColumnCounts.AddRange(NewToolFixedColumnCount);
                    }
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.errorCode = 200;
                    Error.Message = ex.Message;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        public Dictionary<string, string> LoadState()
        {
            Dictionary<string, string> States = new Dictionary<string, string>();
            States = (from state in _context.tblStates where state.Active == true orderby state.StateID select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (States == null)
            {
                States = new Dictionary<string, string>();
            }
            return States;
        }
        public Dictionary<int, string> LoadCity(int CountryID, string StateID)
        {
            Dictionary<int, string> City = new Dictionary<int, string>();
            if (CountryID != 0)
            {
                City = (from city in _context.tblCities where city.CountryID == CountryID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            else
            {
                City = (from city in _context.tblCities where city.StateID == StateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            if (City == null)
                City = new Dictionary<int, string>();
            return City;
        }
        public void Dispose()
        {
            _context.Dispose();
        }
    }
}
